package com.zjy.financialsystemjavafx.service;

import com.zjy.financialsystemjavafx.util.DatabaseUtil;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.time.YearMonth;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * 仪表盘数据服务层，负责从数据库查询各类统计数据
 */
public class DashboardService {
    private final DataSource dataSource;

    public DashboardService() {
        this.dataSource = DatabaseUtil.getDataSource();
    }

    /**
     * 获取用户所有账户的总资产
     */
    public double getTotalAssets(int userId) throws Exception {
        String sql = "SELECT SUM(current_balance) FROM accounts WHERE user_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getDouble(1);
            }
            return 0.0;
        }
    }

    /**
     * 获取指定范围内每月收入或支出数据
     */
    public Map<YearMonth, Double> getMonthlyAmount(int userId, YearMonth startMonth, YearMonth endMonth, String type) throws Exception {
        // 按月统计收入或支出
        String sql = "SELECT YEAR(transaction_date) AS y, MONTH(transaction_date) AS m, SUM(amount) " +
                     "FROM transactions WHERE user_id = ? AND transaction_type = ? AND transaction_date BETWEEN ? AND ? " +
                     "GROUP BY y, m ORDER BY y, m";
        Map<YearMonth, Double> map = new LinkedHashMap<>();
        // 初始化全部月份为0.0
        YearMonth ym = startMonth;
        while (!ym.isAfter(endMonth)) {
            map.put(ym, 0.0);
            ym = ym.plusMonths(1);
        }
        LocalDate startDate = startMonth.atDay(1);
        LocalDate endDate = endMonth.atEndOfMonth();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setString(2, type);
            ps.setDate(3, java.sql.Date.valueOf(startDate));
            ps.setDate(4, java.sql.Date.valueOf(endDate));
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int y = rs.getInt("y");
                int m = rs.getInt("m");
                double total = rs.getDouble(3);
                YearMonth key = YearMonth.of(y, m);
                if (map.containsKey(key)) {
                    map.put(key, total);
                }
            }
        }
        return map;
    }

    /**
     * 按分类统计支出数据
     */
    public Map<String, Double> getExpenseByCategory(int userId) throws Exception {
        String sql = "SELECT c.category_name, SUM(t.amount) FROM transactions t " +
                     "JOIN categories c ON t.category_id = c.category_id " +
                     "WHERE t.user_id = ? AND t.transaction_type = 'Expense' " +
                     "GROUP BY c.category_name";
        Map<String, Double> map = new LinkedHashMap<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                map.put(rs.getString(1), rs.getDouble(2));
            }
        }
        return map;
    }

    /**
     * 获取账户余额分布
     */
    public Map<String, Double> getAccountDistribution(int userId) throws Exception {
        String sql = "SELECT account_name, current_balance FROM accounts WHERE user_id = ?";
        Map<String, Double> map = new LinkedHashMap<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                map.put(rs.getString(1), rs.getDouble(2));
            }
        }
        return map;
    }

    /**
     * 获取借贷概览（借入/借出总额）
     */
    public Map<String, Double> getLoanOverview(int userId) throws Exception {
        String sql = "SELECT loan_type, SUM(amount) FROM loans WHERE user_id = ? AND status = 'Ongoing' GROUP BY loan_type";
        Map<String, Double> map = new LinkedHashMap<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                map.put(rs.getString(1), rs.getDouble(2));
            }
        }
        return map;
    }
} 